package com.hellogood.yaohuo.server.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.hellogood.yaohuo.server.db.ConnectionPool;
import com.hellogood.yaohuo.server.db.DBConnection;
import com.hellogood.yaohuo.server.db.DBHelper;
import com.hellogood.yaohuo.shared.entity.Org;

public class OrgDAO extends DBHelper {
	Logger log = Logger.getLogger(this.getClass());
	@SuppressWarnings("unchecked")
	public List<Org> getOrgListNotC() {
		String hxDbName = ConnectionPool.hxDbName;
		String sql = "SELECT OrgCode,OrgName,OrgType FROM " + hxDbName + "..SubShop WHERE OrgCode NOT LIKE '%C%'";
		List<?> orgList = super.query(sql);
		return (List<Org>) orgList;
	}
	
	public List<String> getOrgByUserQuyu(String userCode, String quyuCode) {
		List<String> orgCodeList = new ArrayList<String>();
		if (userCode == "xsa") {
			List<Org> orgList = getOrgListByQuyuCode(quyuCode);
			if (orgList != null) {
				for (Org org : orgList) {
					orgCodeList.add(org.getOrgCode());
				}
			}
		} else {
			String hxDbName = ConnectionPool.hxDbName;
			String sql = "SELECT q.OrgCode FROM " + hxDbName + "..UserOrg u,QyOrg q WHERE StaffCode = '" + userCode + "' AND QyCode = '" + quyuCode
					+ "' AND u.OrgCode = q.OrgCode";

//			Connection conn = ConnectionPool.getInstance().getConnection();
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				conn = DBConnection.getInstance().getConnection();
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				while (rs.next()) {
					orgCodeList.add(rs.getString("OrgCode"));
				}
			} catch (Exception ex) {
				ex.printStackTrace();
				log.error(ex.getMessage());
			} finally {
				if (rs != null) {
					try {
						rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (ps != null) {
					try {
						ps.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (conn != null) {
//					ConnectionPool.getInstance().release(conn);
				}
			}
		}
		return orgCodeList;
	}
	
	@Override
	public List<Org> initializeObject(ResultSet rs) throws SQLException {
		List<Org> orgList = new ArrayList<Org>();
		if (rs != null) {
			while (rs.next()) {
				Org org = new Org();
				org.setOrgCode(rs.getString("OrgCode") == null ? "" : rs.getString("OrgCode").trim());
				org.setOrgName(rs.getString("OrgName") == null ? "" : rs.getString("OrgName").trim());
				org.setOrgType(rs.getString("OrgType") == null ? "" : rs.getString("OrgType").trim());
				orgList.add(org);
			}
		}
		return orgList;
	}

	public List<Org> getOrgListByUserCode(String userCode) {
		String hxDbName = ConnectionPool.getInstance().hxDbName;
		String sql = "SELECT OrgCode,OrgName,OrgType FROM " + hxDbName + "..SubShop s," + hxDbName + "..UserOrg u WHERE u.OrgCode = s.OrgCode and u.StaffCode = '"
				+ userCode + "'";
		List<?> orgList = super.query(sql);
		return (List<Org>) orgList;
	}

	public List<Org> getOrgList() {
		String hxDbName = ConnectionPool.hxDbName;
		String sql = "SELECT OrgCode,OrgName,OrgType FROM " + hxDbName + "..SubShop WHERE OrgCode NOT LIKE '%C%'";
		List<?> orgList = super.query(sql);
		return (List<Org>) orgList;
	}

	public List<Org> getOrgListByUser(String user) {
		String hxDbName = ConnectionPool.hxDbName;
		String sql = "SELECT OrgCode,OrgName,OrgType FROM " + hxDbName
				+ "..SubShop WHERE OrgCode NOT LIKE '%C%' AND orgCode IN (SELECT orgCode FROM UserOrg WHERE staffCode = '" + user + "')";
		List<?> orgList = super.query(sql);
		return (List<Org>) orgList;
	}

	public List<Org> getCOrgList() {
		String hxDbName = ConnectionPool.hxDbName;
		String sql = "SELECT OrgCode,OrgName,OrgType FROM " + hxDbName + "..SubShop WHERE OrgCode LIKE '%C%' ORDER BY OrgCode";
		List<?> orgList = super.query(sql);
		return (List<Org>) orgList;
	}

	public List<Org> getUnQuyuedList() {
		String hxDbName = ConnectionPool.hxDbName;
		String sql = "SELECT OrgCode,OrgName,OrgType FROM " + hxDbName + "..SubShop s WHERE s.OrgCode NOT IN (SELECT OrgCode FROM QyOrg)";
		List<?> orgList = super.query(sql);
		return (List<Org>) orgList;
	}

	public List<Org> getUnQuyuedOrgOnlyList() {
		String hxDbName = ConnectionPool.hxDbName;
		String sql = "SELECT OrgCode,OrgName,OrgType FROM " + hxDbName + "..SubShop s WHERE s.OrgCode NOT LIKE '%C%' AND s.OrgCode NOT IN (SELECT OrgCode FROM QyOrg)";
		List<?> orgList = super.query(sql);
		return (List<Org>) orgList;
	}

	public List<Org> getOrgListByQuyuCode(String code) {
		String hxDbName = ConnectionPool.hxDbName;
		String sql = "SELECT OrgCode,OrgName,OrgType FROM " + hxDbName
				+ "..SubShop s WHERE s.OrgCode NOT LIKE '%C%' AND s.OrgCode IN (SELECT OrgCode FROM QyOrg WHERE QyCode = '" + code + "')";
		List<?> orgList = super.query(sql);
		return (List<Org>) orgList;
	}

	public Org getOrgByCode(String code) {
		String hxDbName = ConnectionPool.hxDbName;
		String sql = "SELECT OrgCode,OrgName,OrgType FROM " + hxDbName + "..SubShop WHERE OrgCode = '" + code + "'";
		List<?> orgList = super.query(sql);
		if (orgList != null && (!orgList.isEmpty())) {
			return (Org) orgList.get(0);
		}
		return null;
	}
}
